package cn.utils;

import cn.dto.Equipments;
import cn.zyh.dto.BasicInformation;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;

public class AnalysisExcel {


    public ArrayList<BasicInformation> ToAnalysisExcel(String getXls) {



        Workbook workbook = null;
        File Inputfile = new File(getXls);
        // File outputfile = new File("C:\\Users\\Administrator\\Desktop\\car.txt");
        ArrayList<BasicInformation> list = new ArrayList<BasicInformation>();
        ArrayList<Equipments> listEQ = new ArrayList<>();


        // Unable to recognize OLE stream ss不支持xlsx格式 支持xls格式

        try {
            //字符输入流，读取表格
            FileInputStream fileInputStream = new FileInputStream(Inputfile);
            workbook = Workbook.getWorkbook(fileInputStream);
            // FileOutputStream fileOutputStream = new FileOutputStream(outputfile);
            // BufferedOutputStream bw = new BufferedOutputStream(fileOutputStream); //输出语句
            //读取第一个单元薄
            Sheet readfirst = workbook.getSheet(0);
            //有效行
            int rows = readfirst.getRows();
            //有效列
            int clomns = readfirst.getColumns();
            System.out.println("row:" + rows);
            System.out.println("clomns:" + clomns);

            for (int i = 1; i < rows; i++) {
                Cell[] cells = readfirst.getRow(i); //循环得到每一行的单元格对象


                //根据每一个单元格对象的到里面的值
                String sensorId = cells[0].getContents().replaceAll(" ", "");
                if (sensorId=="")break;
                String sensorName = cells[1].getContents().replaceAll(" ", "");
                String sensorType = cells[2].getContents().replaceAll(" ", "");
                String dataCode = cells[3].getContents().replaceAll(" ", "");
                String applyId = cells[4].getContents().replaceAll(" ", "");
                String projectCode = cells[5].getContents().replaceAll(" ", "");
                String cityId = cells[6].getContents().replaceAll(" ", "");
                String areaId = cells[7].getContents().replaceAll(" ", "");
                String regionId = cells[8].getContents().replaceAll(" ", "");
                String commissionName = cells[9].getContents().replaceAll(" ", "");
                String commissionCode = cells[10].getContents().replaceAll(" ", "");
                String address = cells[11].getContents().replaceAll(" ", "");
                String wsLng = cells[12].getContents().replaceAll(" ", "");
                String wsLat = cells[13].getContents().replaceAll(" ", "");
                String connectType = cells[14].getContents().replaceAll(" ", "");
                String saveType = cells[15].getContents().replaceAll(" ", "");

                //将得到的值放在一个我需要的格式的string对象中
                BasicInformation basicInformation = new BasicInformation(sensorId, sensorName, sensorType, dataCode, applyId, projectCode,
                        cityId, areaId, regionId, commissionName, commissionCode, address, wsLng, wsLat, connectType,
                        saveType);
//                String s = "sensorId, sensorName, sensorType, dataCode, applyId, projectCode,cityId, areaId, regionId, commissionName, commissionCode, address, wsLng, wsLat, connectType,saveType";
                list.add(basicInformation);
            }


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return list;


    }




}